package com.example.demo.dao;
import com.example.demo.entity.OrderDetailVo;
import com.example.demo.entity.OrderTotalVo;
import com.example.demo.entity.UserVo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class OrderDetailDao {
    public OrderDetailVo findByOrderDetailID(int orderDetailID) throws Exception{
        OrderDetailVo orderDetailVo=null;
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con= DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select * from OrderDetail where OrderDetailID = ? ");
            ps.setInt(1,orderDetailID);
            rs=ps.executeQuery();
            if (rs.next()){
                    orderDetailVo=new OrderDetailVo();
                orderDetailVo.setOrderDetailID(rs.getInt("OrderDetailID"));
                orderDetailVo.setOrdersID(rs.getInt("OrdersID"));
                orderDetailVo.setBookID(rs.getInt("BookID"));
                orderDetailVo.setQuantity(rs.getInt("Quantity"));
                orderDetailVo.setUnitPrice(rs.getDouble("UnitPrice"));
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return orderDetailVo;
    }

    public void saveOderDetail(OrderDetailVo orderDetailVo) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("insert into OrderDetail (OrdersID,BookID,Quantity,UnitPrice) value (?,?,?,?)");

            ps.setInt(1,orderDetailVo.getOrdersID());
            ps.setInt(2,orderDetailVo.getBookID());
            ps.setInt(3,orderDetailVo.getQuantity());
            ps.setDouble(4,orderDetailVo.getUnitPrice());

            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
    public void updateOrderDetail(OrderDetailVo orderDetailVo) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("update OrderDetail set OrdersID=?,BookID=? ,Quantity=?,UnitPrice=? where OrderDetailID=?");
            ps.setInt(1,orderDetailVo.getOrdersID());
            ps.setInt(2,orderDetailVo.getBookID());
            ps.setInt(3,orderDetailVo.getQuantity());
            ps.setDouble(4,orderDetailVo.getUnitPrice());
            ps.setInt(5,orderDetailVo.getOrderDetailID());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
    public void deleteOrderDetail(int orderDetailID) throws Exception {
        Connection con= null;
        PreparedStatement ps=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("delete  from OrderDetail where  OrderDetailID=?");
            ps.setInt(1,orderDetailID);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
    }
    public List<OrderTotalVo> getOrderDetail() throws Exception{
        List<OrderTotalVo> orderTotalVos=new ArrayList<>();
        OrderTotalVo orderTotalVo=null;
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con= DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("SELECT OrderDetail.OrderDetailID, Book.Title, Book.Price, OrderDetail.Quantity, OrderDetail.UnitPrice, User.Username, Orders.OrderStatus FROM " +
                    "OrderDetail INNER JOIN Orders ON OrderDetail.OrdersID = Orders.OrdersID " +
                    "INNER JOIN Book ON OrderDetail.BookID = Book.BookID " +
                    "INNER JOIN User ON Orders.UserID = User.UserID;"
            );


            rs=ps.executeQuery();
            if (rs.next()){
               orderTotalVo=new OrderTotalVo();
               orderTotalVo.setOrderDetailID(rs.getInt("OrderDetailID"));
               orderTotalVo.setTitle(rs.getString("title"));
               orderTotalVo.setPrice(rs.getDouble("price"));
               orderTotalVo.setQuantity(rs.getInt("Quantity"));
               orderTotalVo.setUnitPrice(rs.getDouble("UnitPrice"));
               orderTotalVo.setUsername(rs.getString("username"));
               orderTotalVo.setOrderStatus(rs.getString("orderStatus"));
               orderTotalVos.add(orderTotalVo);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return orderTotalVos;
    }
    public List<OrderTotalVo> getOrderDetailByUserName(String username) throws Exception{
        List<OrderTotalVo> orderTotalVos=new ArrayList<>();
        OrderTotalVo orderTotalVo=null;
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con= DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps = con.prepareStatement(
                    "SELECT OrderDetail.OrderDetailID, Book.Title, Book.Price, OrderDetail.Quantity, OrderDetail.UnitPrice, User.Username, Orders.OrderStatus FROM " +
                            "OrderDetail INNER JOIN Orders ON OrderDetail.OrdersID = Orders.OrdersID " +
                            "INNER JOIN Book ON OrderDetail.BookID = Book.BookID " +
                            "INNER JOIN User ON Orders.UserID = User.UserID " +
                            "WHERE User.Username LIKE ?;"
            );
            ps.setString(1, "%" + username + "%");

            rs=ps.executeQuery();
            if (rs.next()){
                orderTotalVo=new OrderTotalVo();
                orderTotalVo.setOrderDetailID(rs.getInt("OrderDetailID"));
                orderTotalVo.setTitle(rs.getString("title"));
                orderTotalVo.setPrice(rs.getDouble("price"));
                orderTotalVo.setQuantity(rs.getInt("Quantity"));
                orderTotalVo.setUnitPrice(rs.getDouble("UnitPrice"));
                orderTotalVo.setUsername(rs.getString("username"));
                orderTotalVo.setOrderStatus(rs.getString("orderStatus"));
                orderTotalVos.add(orderTotalVo);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return orderTotalVos;
    }
}
